package cn.ainit.sys.menu;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import org.apache.commons.collections.map.ListOrderedMap;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.ainit.common.ReturnObject;
import cn.ainit.sys.common.dao.SysBaseDao;
import cn.ainit.sys.dz.entity.SysDzSource;
import cn.ainit.sys.query.datastore.DataStore;
import cn.ainit.sys.utils.DateTimeStr;
import cn.ainit.sys.utils.UUID32;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;

@Transactional
@Repository("menuDao")
public class MenuDaoImpl extends SysBaseDao implements MenuDao {

	@Override
	public List<SysFunctions> getAllMenu() {
		String hql = "from SysFunctions s where s.superFuncId = 'root' ORDER BY menuid,sortNum";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		return query.list();
	}

	@Override
	public List<SysFunctions> getMenuById(String funcId) {
		String hql = "from SysFunctions s where s.rootId=? ORDER BY menuid,sortNum ";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, funcId);
		return (List<SysFunctions>) query.list();
	}

	@Override
	public List<SysFunctions> getListByFId(String superId) {
		String hql = "from SysFunctions s where s.superFuncId =? ORDER BY menuid,sortNum  desc";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, superId);
		return query.list();
	}

	@Override
	public List<SysDzSource> getAllDzSource() throws Exception {
		String hql = "from SysDzSource";
		List<SysDzSource> allDzSources = sessionFactory.getCurrentSession()
		.createQuery(hql).list();
		return allDzSources;
	}

	@Override
	public List<SysDzSource> getDzSourceByDBI(String dbi) throws Exception{
		String hql = "from SysDzSource sds where sds.dbi = '" + dbi + "'";
		List<SysDzSource> dbiDzSources = sessionFactory.getCurrentSession().createQuery(hql).list();
		return dbiDzSources;
	}

	@Override
	public List<SysDzSource>getDzSourceByDBIAndShowpk(String dbi, String showpk) throws Exception{
		String hql = "from SysDzSource sds where sds.dbi = '" + dbi + "' and showPk = '" + showpk + "'";
		List<SysDzSource> dbiDzSources = sessionFactory.getCurrentSession().createQuery(hql).list();
		return dbiDzSources;
	}

	@Override
	public List<SysDzSource> getDzSourceByDBIAndShowpkNot(String dbi, String showpk) throws Exception{
		String hql = "from SysDzSource sds where sds.dbi = '" + dbi + "' and showPk <> '" + showpk + "'";
		List<SysDzSource> dbiDzSources = sessionFactory.getCurrentSession().createQuery(hql).list();
		return dbiDzSources;
	}

	@Override
	public List<SysDzSource> getDzSourceByDBIAndShowpkNotIn(String dbi, String showpkNotIn) throws Exception{
		String hql = "from SysDzSource sds where sds.dbi = '" + dbi + "' and showPk not in (" + showpkNotIn + ")";
		List<SysDzSource> dbiDzSources = sessionFactory.getCurrentSession().createQuery(hql).list();
		return dbiDzSources;
	}

	@Override
	public List<SysDzSource> getDzSourceSome(String dsId) throws Exception {
		try {
			DataStore dataStore = new DataStore();
			String hql = "from DataStore d where d.dsId = '" + dsId + "'";
			dataStore = (DataStore) sessionFactory.getCurrentSession()
			.createQuery(hql).list().get(0);
			String updateTable = dataStore.getUpdateTable();
			String hql2 = "from SysDzSource s where upper(s.updateTable)='"
				+ updateTable.toUpperCase() + "'";
			List<SysDzSource> sysDzSources = new ArrayList<SysDzSource>();
			sysDzSources = sessionFactory.getCurrentSession().createQuery(hql2)
			.list();
			if (sysDzSources != null) {
				return sysDzSources;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List loadDzList(String sqlText, String showPk, String showName)
	throws Exception {
		String sql = " select " + showPk + " id," + showName + " name from ("
		+ sqlText + " ) TMP";
		return this.getJdbcTemplate().queryForList(sql);
	}

	@Override
	public SysFunctions getFunctionById(String funcId,String uid) throws Exception {
		SysFunctions tmp = new SysFunctions();
		String sql = " select * from sys_functions SF  where SF.FUNC_ID ='"+funcId+"'";
		List<Map<String, Object>> maps = this.getJdbcTemplate().queryForList(sql);
		if (maps != null && maps.size() == 1) {
			Map<String, Object> objMap = maps.get(0);
			tmp.setPrefix(objMap.get("PREFIX") == null ? "" : objMap.get("PREFIX").toString());
			tmp.setUrl(objMap.get("FUNC_URL") == null ? "" : objMap.get("FUNC_URL").toString());
			tmp.setMenuid(objMap.get("FUNC_ID") == null ? "" : objMap.get("FUNC_ID").toString());
			tmp.setSuperFuncId(objMap.get("SUPER_FUNC_ID") == null ? "": objMap.get("SUPER_FUNC_ID").toString());
			tmp.setRootId(objMap.get("ROOT_ID") == null ? "" : objMap.get("ROOT_ID").toString());
			return tmp;
		}
		return null;
	}

	/**
	 *
	 *获取用户右侧菜单栏的权限
	 */
	@Override
	public Object getLeftMenu(String userId) throws Exception {
		Connection conn=null;
		CallableStatement cs=null;
		ResultSet sysRight = null;
		List list = new ArrayList();  
		try {
			conn=this.getJdbcTemplate().getDataSource().getConnection();
			String callName = "{Call PK_RIGHT.UP_GetSystemRight(?,?)}";
			cs=conn.prepareCall(callName);
			cs.setString(1,userId);
			cs.registerOutParameter(2,OracleTypes.CURSOR);
			cs.execute();
			sysRight=(ResultSet)cs.getObject(2);
			ResultSetMetaData md = sysRight.getMetaData();  
			int columnCount = md.getColumnCount(); 
			while (sysRight.next()) { 
				Map rowData = new HashMap(); 
				for (int i = 1; i <= columnCount; i++) {  
					rowData.put(md.getColumnName(i), sysRight.getObject(i));  
				}
				list.add(rowData);  
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(sysRight!=null)
			{
				sysRight.close();
			}
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return list;
	}

	/**
	 *
	 *获取用户加载子系统的权限
	 */
	@Override
	public Object getSubSysRight(String userId,String menuId) throws Exception {
		Connection conn=null;
		CallableStatement cs=null;
		ResultSet sysRight = null;
		List list = new ArrayList();  
		try {
			conn=this.getJdbcTemplate().getDataSource().getConnection();
			String callName = "{Call PK_RIGHT.UP_GetModualRight(?,?,?)}";
			cs=conn.prepareCall(callName);
			cs.setString(1,userId);
			cs.setString(2,menuId);
			cs.registerOutParameter(3,OracleTypes.CURSOR);
			cs.execute();
			sysRight=(ResultSet)cs.getObject(3);
			ResultSetMetaData md = sysRight.getMetaData();  
			int columnCount = md.getColumnCount(); //Map rowData;  
			while (sysRight.next()) { 
				Map rowData = new HashMap(); 
				for (int i = 1; i <= columnCount; i++) {  
					rowData.put(md.getColumnName(i), sysRight.getObject(i));  
				}
				list.add(rowData);  
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(sysRight!=null)
			{
				sysRight.close();
			}
			if(cs!=null){
				cs.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return list;
	}


	@Override
	public boolean writeSession(StringBuffer sessionSql, SessionObj sessionObj)
	throws Exception {
		sessionSql = new StringBuffer("");
		sessionSql
		.append("insert into SYS_SESSION (SESSION_ID, LOGIN_IP,STATION_ID,DEPART_ID,USER_NAME,O_ID) "
				+ // 登录时间。。。
				"values ('"
				+ UUID32.newGuid()
				+ "','"
				+ sessionObj.getIp()
				+ "','"
				+ sessionObj.getSta()
				+ "','"
				+ sessionObj.getDepid()
				+ "','"
				+ sessionObj.getName()
				+ "','"
				+ sessionObj.getUid()
				+ "')");

		String sql2 = " select count(1) from SYS_SESSION where O_ID = '"
			+ sessionObj.getUid() + "'";
		if (this.getJdbcTemplate().queryForInt(sql2) >= 1) {
			sessionSql = new StringBuffer("");
			sessionSql.append(" update SYS_SESSION set " + " SESSION_ID = '"
					+ sessionObj.getSessionId() + "'," + " LOGIN_IP = '"
					+ sessionObj.getIp() + "'," + " STATION_ID='"
					+ sessionObj.getSta() + "'," + " DEPART_ID = '"
					+ sessionObj.getDepid() + "'," + " USER_NAME = '"
					+ sessionObj.getName() + "' " + " where O_ID = '"
					+ sessionObj.getUid() + "'");
		}
		this.getJdbcTemplate().execute(sessionSql.toString());
		return true;
	}

	@Override
	public SessionObj existOperator(String sql, String name, String pass)
	throws Exception {
		SessionObj sessionObj = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rSet = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, pass);
			rSet = pstmt.executeQuery();
			if (rSet.next()) {
				sessionObj = new SessionObj();
				String uid = (String) rSet.getString("o_id"); // 用户的Id
				String namedb = (String) rSet.getString("o_name");// 用户的名称
				String depid = (String) rSet.getString("bumen");
				String exist = (String) rSet.getObject("o_exist");
				if (uid == null || namedb == null || uid.trim().equals("")
						|| namedb.trim().equals("")) { // 非法用户不能登陆
					return null;
				} else {
					sessionObj.setUid(uid);
					sessionObj.setName(namedb);
					sessionObj.setDepid(depid);
					sessionObj.setO_exist(exist);
				}
			}
			System.out.println("Menu   #L.134# ");
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			if (rSet != null) {
				rSet.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
		return sessionObj;
	}

	@Override
	public List<Map<String, Object>> getAllMenus() {
		String sql = "select * from vv_rights_btns vrb where vrb.rtype='1subsystem'  order by vrb.ordernum asc";
		List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql);
		return list;
	}

	@Override
	public List groupByUId(String uid) throws Exception {
		List list = new ArrayList();
		List list1 = new ArrayList();
		List list2 = new ArrayList();
		try {
			// 原查询用户所属角色组的sql
			// String sql =
			// "SELECT * FROM SYS_RIGHT_GROUP WHERE GROUP_ID IN (SELECT GROUP_ID FROM SYS_RIGHT_MAP WHERE O_ID ='"+uid+"')";
			String sql = "SELECT GROUP_ID FROM SYS_PERMS_USER_GROUP WHERE O_ID ='"
				+ uid + "'";
			list1 = this.jdbcTemplate.queryForList(sql);
			list.add(list1);
			// String sql1 =
			// "SELECT * FROM SYS_RIGHT_GROUP WHERE GROUP_ID NOT IN (SELECT GROUP_ID FROM SYS_RIGHT_MAP WHERE O_ID ='"+uid+"')";
			// list2=this.jdbcTemplate.queryForList(sql1);
			// list.add(list2);
			if (list != null) {
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/***
	 * 根据用户查找用户模块
	 */
	@Override
	public List modualByUId(String uid) throws Exception {
		List list = new ArrayList();
		try {
			String sql = " SELECT * FROM (SELECT GM.MODUAL_ID FROM SYS_PERMS_GROUP_MODUAL GM WHERE GM.GROUP_ID IN ("
				+ " SELECT GROUP_ID FROM SYS_PERMS_USER_GROUP WHERE O_ID ='"
				+ uid
				+ "')  "
				+ " UNION "
				+ " SELECT UM.MODUAL_ID FROM SYS_PERMS_USER_MODUAL UM WHERE UM.O_ID = '"
				+ uid
				+ "' AND UM.FORBIDEN ='0') GUM WHERE GUM.MODUAL_ID NOT IN ( "
				+ " SELECT UM.MODUAL_ID FROM SYS_PERMS_USER_MODUAL UM WHERE UM.O_ID = '"
				+ uid + "' AND UM.FORBIDEN ='1' ) ";
			list = this.jdbcTemplate.queryForList(sql);
			if (list != null) {
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List dataStoreAll() throws Exception {
		List list = new ArrayList();
		try {
			String sql = "SELECT * FROM SYS_DATASTORE ";
			list = this.jdbcTemplate.queryForList(sql);
			if (list != null) {
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List getAllGroup() throws Exception {
		List list = new ArrayList();
		try {
			// String sql = "SELECT * FROM SYS_RIGHT_GROUP"; //原角色组表的SQL
			// String sql =
			// "SELECT G.BUMEN, (SELECT BM.M_NAME FROM BS_MAN BM WHERE BM.MAN_TYPE = 'bumen' AND BM.MAN_ID = G.BUMEN ) BUMEN_NAME,"+
			// "G.GROUP_ID,G.GROUP_NAME,G.GROUP_LEVEL,G.INPUT_DATE,G.INPUT_MAN,G.LAST_MOD_DATE,G.LAST_MOD_MAN,G.MEMO "+
			// "FROM SYS_PERMS_GROUP G ORDER BY G.BUMEN,TO_NUMBER(G.GROUP_LEVEL)";
			String sql = "select sr.role_id,sr.role_name,sr.ordernum from sys_role sr order by sr.ordernum asc";
			list = this.jdbcTemplate.queryForList(sql);
			if (list != null) {
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List getAllDepart() throws Exception {
		List list = new ArrayList();
		try {
			String sql = "SELECT BM.* FROM BS_MAN BM WHERE BM.MAN_TYPE = 'bumen'";
			list = this.jdbcTemplate.queryForList(sql);
			if (list != null) {
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List dsByModualList(String mId, String oid) throws Exception {
		List list = new ArrayList();
		String sql = "";
		String sql1 = "";
		String sql2 = "";
		String sql3 = "";
		List haveDS = new ArrayList();
		List forbidDS = new ArrayList();
		List allDSByMo = new ArrayList();
		try {
			sql = "SELECT * FROM SYS_DATASTORE WHERE DS_ID IN ("
				+ "SELECT DS_ID FROM SYS_RIGHT_MODUAL_DATASTORE WHERE MODUAL_ID = '"
				+ mId
				+ "' AND DS_ID "
				+ " IN"
				+ " (SELECT F_ID FROM SYS_RIGHT_USER_MENUS WHERE O_ID ='"
				+ oid
				+ "' AND FORBIDDEN = '0' "
				+ " UNION "
				+ " SELECT F_ID FROM SYS_RIGHT_GROUP_MENU_MAP WHERE GROUP_ID IN (SELECT GROUP_ID FROM SYS_RIGHT_MAP WHERE O_ID='"
				+ oid
				+ "') AND MENU_ID ='"
				+ mId
				+ "' "
				+ " AND F_ID NOT IN (SELECT F_ID FROM SYS_RIGHT_USER_MENUS WHERE O_ID = '"
				+ oid + "' AND FORBIDDEN = '1')))";

			sql1 = "SELECT * FROM SYS_DATASTORE WHERE DS_ID IN (SELECT DS_ID FROM SYS_RIGHT_MODUAL_DATASTORE WHERE MODUAL_ID = '"
				+ mId + "')";
			sql2 = "SELECT * FROM SYS_DATASTORE WHERE DS_ID IN ("
				+ "SELECT DS_ID FROM SYS_RIGHT_MODUAL_DATASTORE WHERE MODUAL_ID = '"
				+ mId + "' AND DS_ID " + " IN"
				+ " (SELECT F_ID FROM SYS_RIGHT_USER_MENUS WHERE O_ID ='"
				+ oid + "' AND FORBIDDEN = '1' ))";
			/**作者：刘振涛 2015年7月21日18:16:01
			 * 以前的获取所有按钮的语句太长了，没用上视图，让人看不懂
			 * sql3 直接改成下面这样，以后其他的也应该改成这样
			 * 这个语句是 查询所有的模块
			 ***/
			sql3 = "select * from vv_rights_btns vrb where vrb.rtype='2module'";
			if (!"".equals(oid) && !"".equals(mId)) {
				haveDS = this.jdbcTemplate.queryForList(sql); // have
				list.add(haveDS);
				allDSByMo = this.jdbcTemplate.queryForList(sql1); // all
				list.add(allDSByMo);
				forbidDS = this.jdbcTemplate.queryForList(sql2);
				list.add(forbidDS);
			} else if ("".equals(oid) && "".equals(mId)) {
				list = this.jdbcTemplate.queryForList(sql3);
			} else {
				list = this.jdbcTemplate.queryForList(sql1);
			}
			if (list != null) {
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public String saveGroupUser(String oid, List<String> gIds, String uid)
	throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			String delsql = "DELETE FROM SYS_PERMS_USER_GROUP WHERE O_ID ='"
				+ oid + "'";
			boolean result = this.executeSQL(delsql);
			String insertSql = "INSERT INTO SYS_PERMS_USER_GROUP(UG_ID,O_ID,GROUP_ID,INPUT_DATE,INPUT_MAN,LAST_MOD_DATE,LAST_MOD_MAN)"
				+ " VALUES(SYS_GUID(),'"
				+ oid
				+ "',?,?,'"
				+ uid
				+ "',?,'"
				+ uid + "')";
			if (result) {
				// 添加数据
				if (gIds != null) {
					for (int i = 0; i < gIds.size(); i++) {
						pstmt = conn.prepareStatement(insertSql);
						pstmt.setString(1, gIds.get(i));
						pstmt.setDate(2, java.sql.Date.valueOf(DateTimeStr
								.getDateTime()));
						pstmt.setDate(3, java.sql.Date.valueOf(DateTimeStr
								.getDateTime()));
						pstmt.execute();
						pstmt.close();
					}
				}
				return "true";
			} else {
				return "false";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return null;
	}

	@Override
	public String saveModualUser(String oid, String mids, String uid)
	throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			String delsql = "DELETE FROM SYS_RIGHT_USER_MODUAL_MAP WHERE O_ID ='"
				+ oid + "'";
			boolean result = this.executeSQL(delsql);
			String insertSql = "INSERT INTO SYS_RIGHT_USER_MODUAL_MAP(M_ID,O_ID,MODUAL_ID,INPUT_DATE,INPUT_MAN,LAST_MOD_DATE,LAST_MOD_MAN) "
				+ " VALUES(SYS_GUID(),'"
				+ oid
				+ "',?,?,'"
				+ uid
				+ "',?,'"
				+ uid + "')";
			if (result) {
				// 添加数据
				if (mids != "") { // 判断模块Id是否是空
					String[] modualIds = mids.split(";");
					for (int i = 0; i < modualIds.length; i++) {
						pstmt = conn.prepareStatement(insertSql);
						pstmt.setString(1, modualIds[i]);
						pstmt.setDate(2, java.sql.Date.valueOf(DateTimeStr
								.getDateTime()));
						pstmt.setDate(3, java.sql.Date.valueOf(DateTimeStr
								.getDateTime()));
						pstmt.execute();
						pstmt.close();
					}
				}
				return "true";
			} else {
				return "false";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return null;
	}

	@Override
	public String saveDSGN(String mId, String oid,
			List<Map<String, String>> dsList, String uid) throws Exception {
		List<Map<String, Object>> allDsList = new ArrayList<Map<String, Object>>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			// 1.获取该组中该模块中所有的功能
			allDsList = this.dsByGroMo(oid, mId);
			// 2.清除个人数据
			String sql = "DELETE FROM SYS_RIGHT_USER_MENUS WHERE F_ID IN (SELECT DS_ID FROM SYS_RIGHT_MODUAL_DATASTORE WHERE MODUAL_ID ='"
				+ mId + "') AND O_ID = '" + oid + "'";
			boolean result = this.executeSQL(sql);
			// 3.比对获取组中功能和传入的功能
			for (int i = 0; i < dsList.size(); i++) { // 循环传入的功能
				if (allDsList.contains(dsList.get(i).get("gnId"))) { // 看传入功能是否存在该组功能中
					if ("0".equals(dsList.get(i).get("yorn"))) {
						dsList.remove(i);
					}
				}
			}
			// 4.插入数据
			String saveSql = "INSERT INTO SYS_RIGHT_USER_MENUS(R_ID,O_ID,F_ID,INPUT_DATE,INPUT_MAN,LAST_MOD_DATE,LAST_MOD_MAN,FORBIDDEN) "
				+ " VALUES(SYS_GUID(),'"
				+ oid
				+ "',?,?,'"
				+ uid
				+ "',?,'"
				+ uid + "',?)";
			if (result) {
				// 添加数据
				if (mId != "") { // 判断模块Id是否是空
					for (int i = 0; i < dsList.size(); i++) {
						pstmt = conn.prepareStatement(saveSql);
						pstmt.setString(1, dsList.get(i).get("gnId").toString()
								.trim());
						pstmt.setDate(2, java.sql.Date.valueOf(DateTimeStr
								.getDateTime()));
						pstmt.setDate(3, java.sql.Date.valueOf(DateTimeStr
								.getDateTime()));
						pstmt.setString(4, dsList.get(i).get("yorn").toString()
								.trim());
						pstmt.execute();
						pstmt.close();
					}
				}
				return "true";
			} else {
				return "false";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return "false";
	}

	@Override
	public List<Map<String, Object>> dsByGroMo(String oid, String mId)
	throws Exception {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			String sql = "SELECT * FROM SYS_DATASTORE WHERE DS_ID IN (SELECT distinct F_ID FROM SYS_RIGHT_GROUP_MENU_MAP WHERE GROUP_ID IN "
				+ "(SELECT GROUP_ID FROM SYS_RIGHT_MAP WHERE O_ID='"
				+ oid
				+ "') AND MENU_ID ='" + mId + "')";
			list = this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public ReturnObject sqlByModual(String dsId) throws Exception {
		ReturnObject returnObject = new ReturnObject();
		String sqlText = "";
		try {
			String sql1 = "SELECT SQL_TEXT FROM SYS_DATASTORE WHERE DS_ID ='"
				+ dsId + "'";
			String sqlStr = this.jdbcTemplate
			.queryForObject(sql1, String.class);
			returnObject.setInfo(sqlStr);
			String sqlCount = "SELECT COUNT(1) COUNT_NUM FROM SYS_RIGHT_USER_FORBID_DATA WHERE DS_ID ='"
				+ dsId + "'";
			List<Map<String, Object>> countNum = this.jdbcTemplate
			.queryForList(sqlCount);
			String whereStr = "";
			if ("0".equals(countNum.get(0).get("COUNT_NUM").toString())) {
				String sql3 = "SELECT GB.CONFIG_SQL FROM SYS_RIGHT_GROUP_FORBID_DATA GB WHERE GB.DS_ID='"
					+ dsId + "'";
				List<Map<String, Object>> whereConfig = this.jdbcTemplate
				.queryForList(sql3);
				if (((!"".equals(whereConfig)) && (whereConfig != null) && (whereConfig
						.get(0).get("CONFIGSQL") != null))) {
					whereStr = whereConfig.get(0).get("CONFIGSQL").toString();
				} else {
					whereStr = "";
				}
			} else {
				String sql2 = "SELECT NVL(UB.CONFIG_SQL,(SELECT GB.CONFIG_SQL FROM SYS_RIGHT_GROUP_FORBID_DATA GB WHERE GB.DS_ID='"
					+ dsId
					+ "')) CONFIGSQL FROM SYS_RIGHT_USER_FORBID_DATA UB WHERE UB.DS_ID = '"
					+ dsId + "'";
				List<Map<String, Object>> whereList = this.jdbcTemplate
				.queryForList(sql2);
				if (((!"".equals(whereList)) && (whereList != null) && (whereList
						.get(0).get("CONFIGSQL") != null))) {
					whereStr = whereList.get(0).get("CONFIGSQL").toString();
				}
			}
			returnObject.setMsg(whereStr);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnObject;
	}

	@Override
	public boolean executSql(String sql) {
		boolean flag = false;
		try {
			flag = this.executeSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean saveWhere(String dsId, String sql, String oid, String uid)
	throws Exception {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			String sql1 = "DELETE FROM SYS_RIGHT_USER_FORBID_DATA WHERE DS_ID ='"
				+ dsId + "'";
			String sql2 = "INSERT INTO SYS_RIGHT_USER_FORBID_DATA(FD_ID,O_ID,DS_ID,CONFIG_SQL,INPUT_DATE,INPUT_MAN,LAST_MOD_DATE,LAST_MOD_MAN,MEMO) "
				+ "VALUES(SYS_GUID(),'"
				+ oid
				+ "','"
				+ dsId
				+ "','"
				+ sql
				+ "',?,'" + uid + "',?,'" + uid + "','" + sql + "')";
			boolean resultFlag = this.executeSQL(sql1);
			if (resultFlag) {
				if (!"".equals(sql)) {
					pstmt = conn.prepareStatement(sql2);
					pstmt.setDate(1, java.sql.Date.valueOf(DateTimeStr
							.getDateTime()));
					pstmt.setDate(2, java.sql.Date.valueOf(DateTimeStr
							.getDateTime()));
					pstmt.execute();
					pstmt.close();
					flag = true;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return flag;
	}

	@Override
	public List butnByDS(String dsId, String oid) throws Exception {
		List list = new ArrayList();
		String sql = "";
		String sql1 = "";
		String sql2 = "";
		String sql3 = "";
		List haveButn = new ArrayList();
		List forbidButn = new ArrayList();
		List allButnByDS = new ArrayList();
		try {
			sql = "SELECT *  FROM (SELECT A.DS_ID, A.BUTN_NAME, A.BUT_CODE FROM SYS_BUTNS A WHERE A.ISSHOW = 'T' "
				+ " UNION "
				+ " SELECT B.DS_ID, B.BUTN_NAME, B.BUT_CODE FROM SYS_BUTTONS B WHERE B.ISSHOW = 'T') BUTNS "
				+ " WHERE BUTNS.BUT_CODE IN (SELECT BUTN_CODE FROM SYS_RIGHT_DS_BUTN  WHERE DS_ID = '"
				+ dsId
				+ "' AND BUTN_CODE IN"
				+ " (SELECT BUTN_CODE  FROM SYS_RIGHT_USER_BUTN  WHERE O_ID = '"
				+ oid
				+ "'  AND FORBIDDEN = '0' "
				+ " UNION "
				+ " SELECT BUTN_CODE FROM SYS_RIGHT_GROUP_BUTN WHERE GROUP_ID IN (SELECT GROUP_ID FROM SYS_RIGHT_MAP WHERE O_ID = '"
				+ oid
				+ "')"
				+ " AND DS_ID = '"
				+ dsId
				+ "' AND BUTN_CODE NOT IN (SELECT BUTN_CODE FROM SYS_RIGHT_USER_BUTN  WHERE O_ID = '"
				+ oid + "' AND FORBIDDEN = '1')))";

			sql1 = "SELECT * FROM (SELECT A.DS_ID, A.BUTN_NAME, A.BUT_CODE FROM SYS_BUTNS A WHERE A.ISSHOW = 'T' "
				+ " UNION "
				+ " SELECT B.DS_ID, B.BUTN_NAME, B.BUT_CODE FROM SYS_BUTTONS B WHERE B.ISSHOW = 'T') BUTN WHERE BUTN.BUT_CODE IN "
				+ " (SELECT BUTN_CODE FROM SYS_RIGHT_DS_BUTN WHERE DS_ID = '"
				+ dsId + "')";

			sql2 = "SELECT *  FROM (SELECT A.DS_ID, A.BUTN_NAME, A.BUT_CODE FROM SYS_BUTNS A WHERE A.ISSHOW = 'T' "
				+ " UNION "
				+ " SELECT B.DS_ID, B.BUTN_NAME, B.BUT_CODE FROM SYS_BUTTONS B WHERE B.ISSHOW = 'T') BUTNS WHERE BUTNS.BUT_CODE IN ( "
				+ " SELECT BUTN_CODE FROM SYS_RIGHT_DS_BUTN WHERE DS_ID ='"
				+ dsId
				+ "' AND BUTN_CODE IN ( "
				+ " SELECT BUTN_CODE FROM SYS_RIGHT_USER_BUTN WHERE O_ID ='"
				+ oid + "' AND FORBIDDEN = '1' ))";
			/**作者：刘振涛 2015年7月21日18:16:01
			 * 以前的获取所有按钮的语句太长了，没用上视图，让人看不懂
			 * sql3 直接改成下面这样，以后其他的也应该改成这样
			 ***/
			sql3 = "SELECT * FROM VV_RIGHTS_BTNS VR WHERE VR.rtype='3gridpagebts'";
			if (!"".equals(oid) && !"".equals(dsId)) {
				haveButn = this.jdbcTemplate.queryForList(sql); // have
				list.add(haveButn);
				allButnByDS = this.jdbcTemplate.queryForList(sql1); // all
				list.add(allButnByDS);
				forbidButn = this.jdbcTemplate.queryForList(sql2);
				list.add(forbidButn);
			} else if ("".equals(oid) && "".equals(dsId)) {
				list = this.jdbcTemplate.queryForList(sql3);
			} else {
				list = this.jdbcTemplate.queryForList(sql1);
			}
			if (list != null) {
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List commButn() throws Exception {
		List list = new ArrayList();
		try {
			String sql = "select * from vv_rights_btns vrb where vrb.rtype='4detailpagebts'";
			list = this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public String saveDSButn(String dsId, String oid,
			List<Map<String, String>> dsList, String uid) throws Exception {
		List<Map<String, Object>> allDsList = new ArrayList<Map<String, Object>>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			// 1.获取该组中该模块中所有的功能
			allDsList = this.butnByGroDs(oid, dsId);
			// 2.清除个人数据
			String sql = "DELETE FROM SYS_RIGHT_USER_BUTN  WHERE BUTN_CODE IN (SELECT BUTN_CODE FROM SYS_RIGHT_DS_BUTN WHERE DS_ID = '"
				+ dsId + "') AND O_ID = '" + oid + "'";
			boolean result = this.executeSQL(sql);
			// 3.比对获取组中功能和传入的功能
			for (int i = 0; i < dsList.size(); i++) { // 循环传入的功能
				if (allDsList.contains(dsList.get(i).get(""))) { // 看传入功能是否存在该组功能中
					if ("0".equals(dsList.get(i).get("yorn"))) {
						dsList.remove(i);
					}
				}
			}
			// 4.插入数据
			String saveSql = "INSERT INTO SYS_RIGHT_USER_BUTN(B_ID,O_ID,BUTN_ID,BUTN_NAME,BUTN_CODE,INPUT_DATE,INPUT_MAN,LAST_MOD_DATE,LAST_MOD_MAN,DS_ID,FORBIDDEN)"
				+ " VALUES(SYS_GUID(),'"
				+ oid
				+ "',?,?,?,?,'"
				+ uid
				+ "',?,'" + uid + "',?,?)";
			if (result) {
				// 添加数据
				if (!"".equals(dsId)) { // 判断模块Id是否是空
					for (int i = 0; i < dsList.size(); i++) {
						pstmt = conn.prepareStatement(saveSql);
						pstmt.setString(1, dsList.get(i).get("butnId")
								.toString().trim());
						pstmt.setString(2, dsList.get(i).get("butnName")
								.toString().trim());
						pstmt.setString(3, dsList.get(i).get("butnCode")
								.toString().trim());
						pstmt.setDate(4, java.sql.Date.valueOf(DateTimeStr
								.getDateTime()));
						pstmt.setDate(5, java.sql.Date.valueOf(DateTimeStr
								.getDateTime()));
						pstmt.setString(6, dsId);
						pstmt.setString(7, dsList.get(i).get("yorn").toString()
								.trim());
						pstmt.execute();
						pstmt.close();
					}
				}
				return "true";
			} else {
				return "false";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return "false";
	}

	@Override
	public List<Map<String, Object>> butnByGroDs(String oid, String dsId)
	throws Exception {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			String sql = "SELECT * FROM (SELECT A.DS_ID, A.BUTN_NAME, A.BUT_CODE FROM SYS_BUTNS A WHERE A.ISSHOW = 'T' "
				+ "UNION "
				+ "SELECT B.DS_ID, B.BUTN_NAME, B.BUT_CODE FROM SYS_BUTTONS B WHERE B.ISSHOW = 'T') BUTN WHERE BUTN.BUT_CODE IN "
				+ "(SELECT BUTN_CODE FROM SYS_RIGHT_GROUP_BUTN WHERE GROUP_ID IN "
				+ "(SELECT GROUP_ID FROM SYS_RIGHT_MAP WHERE O_ID ='"
				+ oid
				+ "') AND DS_ID = '" + dsId + "')";
			list = this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List mgbByGroup(String gid) throws Exception {
		List list = new ArrayList();
		//		List modualList = new ArrayList(); // 模块集合
		//		List dsList = new ArrayList(); // 功能集合
		//		List butnList = new ArrayList(); // 按钮集合
		//		String[] gids = gid.split(";");
		//		StringBuffer allGids = new StringBuffer();
		//		for (int i = 0; i < gids.length; i++) {
		//			allGids.append("'" + gids[i] + "'");
		//			if (i < gids.length - 1) {
		//				allGids.append(",");
		//			}
		//		}
		try {
			// 组中模块
			// String sql1 =
			// "SELECT * FROM SYS_LEFT_MENU WHERE MENU_ID IN (SELECT MODUAL_ID FROM SYS_RIGHT_MODUAL_MAP WHERE GROUP_ID ='"+gid+"')";
			//			String sql1 = "SELECT GM.MODUAL_ID FROM SYS_PERMS_GROUP_MODUAL GM WHERE GM.GROUP_ID in ("
			//					+ allGids.toString() + ")";
			//			// 组中功能
			//			// String sql2 =
			//			// "SELECT * FROM SYS_DATASTORE WHERE DS_ID IN (SELECT F_ID FROM SYS_RIGHT_GROUP_MENU_MAP WHERE GROUP_ID ='"+gid+"')";
			//			// String sql2 =
			//			// "SELECT A.FUNC_ID,B.MODUAL_ID FROM SYS_PERMS_GROUP_MODUAL_FUNC A  LEFT JOIN SYS_PERMS_GROUP_MODUAL B ON A.GM_ID = B.GM_ID WHERE B.GROUP_ID in ("+allGids.toString()+")";
			//			String sql2 = "SELECT MODUAL_ID,FUNC_ID FROM SYS_PERMS_MODUAL_FUNC WHERE FUNC_ID IN (SELECT FUNC_ID FROM SYS_PERMS_GROUP_MODUAL_FUNC WHERE GROUP_ID IN ("
			//					+ allGids.toString() + "))";
			//			// 组中按钮
			//			// String sql3 =
			//			// "SELECT * FROM SYS_BUTNS WHERE BUT_CODE IN (SELECT BUTN_CODE FROM SYS_RIGHT_GROUP_BUTN WHERE GROUP_ID = '"+gid+"')";
			//			// String sql3 =
			//			// "SELECT A.*,B.FUNC_ID, C.MODUAL_ID FROM SYS_PERMS_GROUP_FUNC_BUTN A "+
			//			// "LEFT JOIN SYS_PERMS_GROUP_MODUAL_FUNC B ON A.GMF_ID = B.GMF_ID "+
			//			// "LEFT JOIN SYS_PERMS_GROUP_MODUAL C ON B.GM_ID = C.GM_ID WHERE C.GROUP_ID in ("+allGids.toString()+")";
			//			String sql3 = " SELECT FUNC_ID, BUTN_ID, BUTN_CODE FROM SYS_PERMS_GROUP_FUNC_BUTN WHERE GROUP_ID IN ("
			//					+ allGids.toString() + ") AND FUNC_ID IS NOT NULL";
			//			// "SELECT FUNC_ID,BUTN_ID,BUTN_CODE FROM SYS_PERMS_FUNC_BUTN WHERE BUTN_CODE IN(SELECT BUTN_CODE FROM SYS_PERMS_GROUP_FUNC_BUTN WHERE GROUP_ID IN ("+allGids.toString()+") AND FUNC_ID IS NULL )"
			//			modualList = this.jdbcTemplate.queryForList(sql1);
			//			list.add(modualList);
			//			dsList = this.jdbcTemplate.queryForList(sql2);
			//			list.add(dsList);
			//			butnList = this.jdbcTemplate.queryForList(sql3);
			//			list.add(butnList);


			String sql = " select * from sys_role_right  s where s.ssid is not null and s.pageid is not null  and s.moduleid is not null and s.btid is not null and  s.role_id='"
				+ gid + "'";

			list = this.jdbcTemplate.queryForList(sql);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List mgbByGroupQX(String qxgid, List<String> gidList)
	throws Exception {
		List list = new ArrayList();
		List modualList = new ArrayList(); // 模块集合
		List dsList = new ArrayList(); // 功能集合
		List butnList = new ArrayList(); // 按钮集合
		try {
			StringBuffer sql1 = new StringBuffer(
					"SELECT * FROM SYS_LEFT_MENU WHERE MENU_ID IN (SELECT MODUAL_ID FROM (SELECT MODUAL_ID FROM SYS_RIGHT_MODUAL_MAP WHERE GROUP_ID = '"
					+ qxgid
					+ "') MODUAL WHERE MODUAL_ID NOT IN (SELECT MODUAL_ID FROM SYS_RIGHT_MODUAL_MAP WHERE GROUP_ID in (");
			StringBuffer sql2 = new StringBuffer(
					"SELECT * FROM SYS_DATASTORE WHERE DS_ID IN (SELECT F_ID FROM (SELECT F_ID FROM SYS_RIGHT_GROUP_MENU_MAP WHERE GROUP_ID ='"
					+ qxgid
					+ "') MENUMAP WHERE MENUMAP.F_ID NOT IN (SELECT F_ID FROM SYS_RIGHT_GROUP_MENU_MAP WHERE GROUP_ID IN (");
			StringBuffer sql3 = new StringBuffer(
					"SELECT * FROM SYS_BUTNS WHERE BUT_CODE IN (SELECT BUTN_CODE FROM (SELECT BUTN_CODE FROM SYS_RIGHT_GROUP_BUTN WHERE GROUP_ID ='"
					+ qxgid
					+ "') BUTN WHERE BUTN.BUTN_CODE NOT IN (SELECT BUTN_CODE FROM SYS_RIGHT_GROUP_BUTN WHERE GROUP_ID IN (");
			StringBuffer sqlStr = new StringBuffer();
			for (int i = 0; i < gidList.size(); i++) {
				sqlStr.append("'" + gidList.get(i) + "'");
				if (i < gidList.size() - 1) {
					sqlStr.append(",");
				}
			}
			sql1.append(sqlStr + ")))");
			sql2.append(sqlStr + ")))");
			sql3.append(sqlStr + ")))");
			modualList = this.jdbcTemplate.queryForList(sql1.toString());
			list.add(modualList);
			dsList = this.jdbcTemplate.queryForList(sql2.toString());
			list.add(dsList);
			butnList = this.jdbcTemplate.queryForList(sql3.toString());
			list.add(butnList);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List gbByUserId(String uid) throws Exception {
		List list = new ArrayList();
		List gnList = new ArrayList();
		List butnList = new ArrayList();
		try {
			// 原查询用户拥有的功能（去除拒绝的情况下）
			// String sql1 =
			// "SELECT *  FROM SYS_DATASTORE WHERE DS_ID IN (SELECT DS_ID FROM SYS_RIGHT_MODUAL_DATASTORE "+
			// " WHERE DS_ID IN (SELECT F_ID FROM SYS_RIGHT_USER_MENUS WHERE O_ID = '"+uid+"' AND FORBIDDEN = '0' "+
			// " UNION SELECT F_ID FROM SYS_RIGHT_GROUP_MENU_MAP WHERE GROUP_ID IN "+
			// " (SELECT GROUP_ID FROM SYS_RIGHT_MAP WHERE O_ID = '"+uid+"') AND F_ID NOT IN (SELECT F_ID "+
			// " FROM SYS_RIGHT_USER_MENUS WHERE O_ID = '"+uid+"' AND FORBIDDEN = '1')))";

			String sql1 = "SELECT MODUAL_ID, FUNC_ID  FROM SYS_PERMS_MODUAL_FUNC WHERE FUNC_ID IN (SELECT FUNC_ID FROM "
				+ "((SELECT FUNC_ID FROM SYS_PERMS_GROUP_MODUAL_FUNC WHERE GROUP_ID IN (SELECT GROUP_ID FROM SYS_PERMS_USER_GROUP  WHERE O_ID = '"
				+ uid
				+ "')) "
				+ " UNION "
				+ " (SELECT FUNC_ID FROM SYS_PERMS_USER_MODUAL_FUNC MF WHERE MF.O_ID = '"
				+ uid
				+ "' AND MF.FORBIDEN = '0')) UMF "
				+ " WHERE UMF.FUNC_ID NOT IN (SELECT FUNC_ID  FROM SYS_PERMS_USER_MODUAL_FUNC MF  WHERE MF.O_ID = '"
				+ uid + "' AND MF.FORBIDEN = '1')) ";
			// String sql1 =
			// "SELECT * FROM ( SELECT C.UM_ID GM_ID,C.FUNC_ID,D.MODUAL_ID FROM SYS_PERMS_USER_MODUAL_FUNC C LEFT JOIN SYS_PERMS_USER_MODUAL D ON C.UM_ID = D.UM_ID "+
			// " WHERE D.O_ID = '"+uid+"' AND C.FORBIDEN = '0') GU WHERE GU.FUNC_ID NOT IN ( "+
			// " SELECT C.FUNC_ID FROM SYS_PERMS_USER_MODUAL_FUNC C LEFT JOIN SYS_PERMS_USER_MODUAL D ON C.UM_ID = D.UM_ID "+
			// " WHERE D.O_ID = '"+uid+"' AND C.FORBIDEN = '1')";
			gnList = this.jdbcTemplate.queryForList(sql1);
			list.add(gnList.size() == 0 ? new ArrayList() : gnList);

			// String sql2 =
			// "SELECT FUNC_ID ,BUTN_ID ,BUTN_CODE FROM SYS_PERMS_FUNC_BUTN WHERE BUTN_CODE IN ( "+
			// " SELECT BUTN_CODE FROM((SELECT BUTN_CODE FROM SYS_PERMS_GROUP_FUNC_BUTN WHERE GROUP_ID IN "
			// +
			// "(SELECT GROUP_ID FROM SYS_PERMS_USER_GROUP WHERE O_ID ='"+uid+"')) "+
			// " UNION "+
			// " (SELECT BUTN_CODE FROM SYS_PERMS_USER_FUNC_BUTN WHERE O_ID ='"+uid+"' AND FORBIDEN ='0')) UFB "+
			// " WHERE UFB.BUTN_CODE NOT IN(SELECT BUTN_CODE FROM SYS_PERMS_USER_FUNC_BUTN WHERE O_ID ='"+uid+"' AND FORBIDEN ='1'))";
			String sql2 = "SELECT FUNC_ID ,BUTN_ID,BUTN_CODE FROM "
				+ " (SELECT G.FUNC_ID,G.BUTN_ID,G.BUTN_CODE  FROM SYS_PERMS_GROUP_FUNC_BUTN G WHERE GROUP_ID IN "
				+ " (SELECT GROUP_ID FROM SYS_PERMS_USER_GROUP WHERE O_ID ='"
				+ uid
				+ "') "
				+ " UNION "
				+ " SELECT U.FUNC_ID,U.BUTN_ID,U.BUTN_CODE FROM SYS_PERMS_USER_FUNC_BUTN U WHERE O_ID ='"
				+ uid
				+ "' AND FORBIDEN ='0' ) FB"
				+ " WHERE FB.BUTN_CODE NOT IN(SELECT BUTN_CODE FROM SYS_PERMS_USER_FUNC_BUTN F "
				+ " WHERE F.O_ID ='"
				+ uid
				+ "' AND FORBIDEN ='1' AND F.BUTN_ID = FB.BUTN_ID AND F.FUNC_ID = FB.FUNC_ID )";
			butnList = this.jdbcTemplate.queryForList(sql2);
			list.add(butnList.size() == 0 ? new ArrayList() : butnList);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List userByGroup(String gId) throws Exception {
		List list = new ArrayList();
		try {
			String sql = "select  bo.o_id,bo.o_name,bo.bumen  from bs_operators bo where  bo.o_exist = '1' and bo.o_id  in (select sru.user_id  from sys_role_user sru where  sru.role_id='"+gId+"') ";
			list = this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List groupById(String gId) throws Exception {
		List list = new ArrayList();
		try {
			String sql = " select * from sys_role_right  s where s.pageid is null and  s.role_id='"
				+ gId + "'";
			list = this.jdbcTemplate.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean execGroup(String gId, String gName, String gLevel, String oid)
	throws Exception {
		String[] id = gId.split(";");
		String[] name = gName.split(";");
		String[] level = gLevel.split(";");

		try {
			for (int i = 0; i < name.length; i++) {
				String insertSql = "INSERT INTO sys_role(role_id,role_name,ordernum,INPUT_DATE,INPUT_MAN,LAST_MOD_DATE,LAST_MOD_MAN) VALUES(SYS_GUID(),'"
					+ name[i]
					       + "','"
					       + level[i]
					               + "',?,'"
					               + oid
					               + "',?,'"
					               + oid + "')";
				String delSql = "DELETE FROM sys_role WHERE role_id = '"
					+ id[i] + "'";
				String updSql = "INSERT INTO sys_role(role_id,role_name,ordernum,INPUT_DATE,INPUT_MAN,LAST_MOD_DATE,LAST_MOD_MAN) VALUES('"
					+ id[i]
					     + "','"
					     + name[i]
					            + "','"
					            + level[i]
					                    + "',?,'"
					                    + oid + "',?,'" + oid + "')";
				if ("null".equals(id[i]) || "".equals(id[i])
						|| "undefined".equals(id[i])) {
					this.jdbcTemplate.update(insertSql, new Object[] {
							java.sql.Date.valueOf(DateTimeStr.getDateTime()),
							java.sql.Date.valueOf(DateTimeStr.getDateTime()) });
				} else {
					boolean result = this.jdbcTemplate.update(delSql) > 0 ? true
							: false;
					if (result) {
						this.jdbcTemplate.update(updSql, new Object[] {
								java.sql.Date
								.valueOf(DateTimeStr.getDateTime()),
								java.sql.Date
								.valueOf(DateTimeStr.getDateTime()) });
					} else {
						return false;
					}
				}
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return false;

	}

	@Override
	public String saveGroupPerms(String gId, PermsGroup allList, String uid)
	throws Exception {
		List<Object[]> list = null;
		List<String> mList = new ArrayList<String>();
		List<List<String>> gList = new ArrayList<List<String>>();
		List<List<String>> bList = new ArrayList<List<String>>();
		String everyFlag = "";
		try {
			everyFlag = UUID32.newGuid();
			mList = allList.getmList(); // 模块List
			gList = allList.getgList(); // 功能List
			bList = allList.getbList(); // 按钮List
			// 往缓存表中插数据，为组和模块
			String sql = " INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,GROUP_ID,MODUAL_ID,FLAG,EVERY_FLAG) VALUES(SYS_GUID(),'"
				+ gId + "',?,'0','" + everyFlag + "')";
			// 往缓存表中插数据，为组，模块，功能
			String sql2 = " INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,GROUP_ID,MODUAL_ID,FUNC_ID,FLAG,EVERY_FLAG) VALUES(SYS_GUID(),'"
				+ gId + "',?,?,'1','" + everyFlag + "')";
			// 往缓存表中插数据，为组，模块，功能，按钮Id，按钮Code
			String sql3 = "INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,GROUP_ID,MODUAL_ID,FUNC_ID,BUTN_ID,BUTN_CODE,FLAG,EVERY_FLAG,BUTN_FLAG) VALUES(SYS_GUID(),'"
				+ gId + "',?,?,?,?,'2','" + everyFlag + "',?)";
			list = new ArrayList<Object[]>();
			for (int i = 0; i < mList.size(); i++) { // 循环模块List
				list.add(new Object[] { mList.get(i).toString() });
			}
			this.jdbcTemplate.batchUpdate(sql, list);
			list = new ArrayList<Object[]>();
			for (int i = 0; i < gList.size(); i++) { // 循环功能List
				list.add(new Object[] { gList.get(i).get(0).toString(),
						gList.get(i).get(1).toString() });
			}
			this.jdbcTemplate.batchUpdate(sql2, list);
			list = new ArrayList<Object[]>();
			for (int i = 0; i < bList.size(); i++) {
				list.add(new Object[] { bList.get(i).get(0).toString(),
						bList.get(i).get(1).toString(),
						bList.get(i).get(2).toString(),
						bList.get(i).get(3).toString(),
						bList.get(i).get(4).toString() });
			}
			this.jdbcTemplate.batchUpdate(sql3, list);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return everyFlag;
		}
	}



	@Override
	public boolean execSaveGroup(String gId, String allList, String uid)
	throws Exception {
		Gson gson=new Gson();
		List<Map<String, Object>> list=gson.fromJson(allList, new TypeToken<List<ListOrderedMap>>() {}.getType());
		Connection conn = null;
		PreparedStatement stmt = null;
		PreparedStatement delStmtTemp=null;
		PreparedStatement delStmt=null;
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			conn.setAutoCommit(false);
			//先删除对应角色所有的权限
			if(list!=null&&list.size()>0){
				String delSql=" DELETE FROM SYS_ROLE_RIGHT_TMP SR  WHERE SR.ROLE_ID ='"+gId+"'";
				delStmtTemp = conn.prepareStatement(delSql);
				delStmtTemp.execute();
			}
			if(list!=null&&list.size()>0){
				String delSql=" DELETE FROM SYS_ROLE_RIGHT SR  WHERE SR.ROLE_ID ='"+gId+"'";
				delStmt = conn.prepareStatement(delSql);
				delStmt.execute();
			}

			//批量插入临时表
			if(list!=null&&list.size()>0){
				String sql = " INSERT INTO SYS_ROLE_RIGHT (role_id,rtype,ssid,moduleid,pageid,btid,col,right_value) "
					+"VALUES (?,?,?,?,?,?,?,?)";
				stmt = conn.prepareStatement(sql);
				for (int i = 0; i < list.size(); i++) {
					stmt.setString(1,gId);
					stmt.setString(2, list.get(i).get("RTYPE")==null?"": list.get(i).get("RTYPE").toString());
					stmt.setString(3, list.get(i).get("SSID")==null?"":list.get(i).get("SSID").toString());
					stmt.setString(4,list.get(i).get("MODULEID")==null?"":list.get(i).get("MODULEID").toString());
					stmt.setString(5,list.get(i).get("PAGEID")==null?"":list.get(i).get("PAGEID").toString());
					stmt.setString(6,list.get(i).get("BTID")==null?"":list.get(i).get("BTID").toString());
					stmt.setString(7, list.get(i).get("COL")==null?"":list.get(i).get("COL").toString()); 
					stmt.setString(8,"1"); 
					stmt.addBatch();
				}
				int [] counts = stmt.executeBatch(); 
			}
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			if (delStmtTemp != null) {
				delStmtTemp.close();
			}
			if (delStmt != null) {
				delStmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return false;
	}


	/**
	 * 
	 * @Title: getOracleArray
	 * @author : FuXiangYi  
	 * @Description:  把list集合转换为array类型参数
	 * @param con
	 * @param parmName 存储过程参数名称
	 * @param parmTypeNmae 存储过程参数类型名称
	 * @param objlist list集合参数
	 * @return
	 * @throws Exception    
	 * @return ARRAY 
	 * @date 2015-5-8 下午01:39:02
	 */
	private  ARRAY getOracleArray(Connection con, String parmName,
			String parmTypeNmae, List<Map<String, Object>> objlist)
	throws Exception {
		ARRAY list = null;
		if (objlist != null && objlist.size() > 0) {
			StructDescriptor structdesc = new StructDescriptor(parmTypeNmae,
					con.unwrap(OracleConnection.class)); // 存储过程名称和连接对象
			STRUCT[] structs = new STRUCT[objlist.size()];
			Object [] result = null;
			int j = 0;
			for (int i = 0; i < objlist.size(); i++) {
				result = new Object[objlist.get(i).size()];
				j = 0;
				for (Map.Entry<String, Object> entry : objlist.get(i)
						.entrySet()) {
					result[j] = entry.getValue();
					j++;
				}
				structs[i] = new STRUCT(structdesc, con.unwrap(OracleConnection.class), result);
			}
			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(parmName,
					con.unwrap(OracleConnection.class));
			list = new ARRAY(desc,  con.unwrap(OracleConnection.class), structs);
			System.out.println(structs);
			System.out.println(list);
		} else {
			ArrayDescriptor desc = ArrayDescriptor.createDescriptor(parmName,
					con.unwrap(OracleConnection.class));
			STRUCT[] structs = new STRUCT[0];
			list = new ARRAY(desc, con.unwrap(OracleConnection.class), structs);
		}
		return list;
	}



	// @Override
	// public boolean execSaveGroup(String gId, String everyFlag, String uid)
	// throws Exception {
	// Connection conn = null;
	// CallableStatement cs = null;
	// try {
	// conn = this.getJdbcTemplate().getDataSource().getConnection();
	// String sql4 = "{Call PK_SYS_PERMS.up_sysRight(?,?,?,?,?,?)}";
	// cs = conn.prepareCall(sql4);
	// cs.setString(1,gId);
	// cs.setString(2,"");
	// cs.setString(3,"0");
	// cs.setString(4,uid);
	// cs.setString(5,everyFlag);
	// cs.registerOutParameter(6, Types.VARCHAR);
	// cs.executeUpdate();
	// conn.commit();
	// cs.execute();
	// String delSql =
	// "DELETE FROM SYS_PERMS_MEMORY WHERE EVERY_FLAG='"+everyFlag+"'";
	// boolean result= this.jdbcTemplate.update(delSql)>0?true:false;
	// if(result){
	// return true;
	// }else{
	// return false;
	// }
	// } catch (Exception e) {
	// e.printStackTrace();
	// }finally{
	// if (cs != null) {
	// cs.close();
	// }
	// if(conn != null){
	// conn.close();
	// }
	// }
	// return false;
	// }

	@Override
	public boolean saveUserPerms(String oid, PermsGroup allList, String uid)
	throws Exception {
		Connection conn = null;
		CallableStatement cs = null;
		PreparedStatement pstmt = null;
		List<String> mList = new ArrayList<String>();
		List<List<String>> gList = new ArrayList<List<String>>();
		List<List<String>> bList = new ArrayList<List<String>>();
		String flagStr = "";
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			String everyFlag = UUID32.newGuid();
			mList = allList.getmList(); // 模块List
			gList = allList.getgList(); // 功能List
			bList = allList.getbList(); // 按钮List
			flagStr = allList.getFlagStr();
			// 往缓存表中插数据，为用户和模块
			String sql = " INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,O_ID,MODUAL_ID,FLAG,EVERY_FLAG) VALUES(SYS_GUID(),'"
				+ oid + "',?,'0','" + everyFlag + "')";
			// 往缓存表中插数据，为用户，模块，功能
			String sql2 = " INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,O_ID,MODUAL_ID,FUNC_ID,FLAG,EVERY_FLAG) VALUES(SYS_GUID(),'"
				+ oid + "',?,?,'1','" + everyFlag + "')";
			// 往缓存表中插数据，为用户，模块，功能，按钮Id，按钮Code
			String sql3 = "INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,O_ID,MODUAL_ID,FUNC_ID,BUTN_ID,BUTN_CODE,FLAG,EVERY_FLAG,BUTN_FLAG) VALUES(SYS_GUID(),'"
				+ oid + "',?,?,?,?,'2','" + everyFlag + "',?)";
			for (int i = 0; i < mList.size(); i++) { // 循环模块List
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, mList.get(i).toString());
				pstmt.execute();
				pstmt.close();
			}
			for (int i = 0; i < gList.size(); i++) { // 循环功能List
				pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1, gList.get(i).get(0).toString());
				pstmt.setString(2, gList.get(i).get(1).toString());
				pstmt.execute();
				pstmt.close();
			}
			for (int i = 0; i < bList.size(); i++) {
				pstmt = conn.prepareStatement(sql3);
				pstmt.setString(1, bList.get(i).get(0).toString());
				pstmt.setString(2, bList.get(i).get(1).toString());
				pstmt.setString(3, bList.get(i).get(2).toString());
				pstmt.setString(4, bList.get(i).get(3).toString());
				pstmt.setString(5, bList.get(i).get(4).toString());
				pstmt.execute();
				pstmt.close();
			}
			String sql4 = "{Call PK_SYS_PERMS.up_sysRight(?,?,?,?,?,?)}";
			cs = conn.prepareCall(sql4);
			cs.setString(1, "");
			cs.setString(2, oid);
			cs.setString(3, flagStr);
			cs.setString(4, uid);
			cs.setString(5, everyFlag);
			cs.registerOutParameter(6, Types.VARCHAR);
			cs.execute();
			String delSql = "DELETE FROM SYS_PERMS_MEMORY WHERE EVERY_FLAG='"
				+ everyFlag + "'";
			boolean result = this.executeSQL(delSql);
			if (result) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			if (cs != null) {
				cs.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Override
	public RightList findPerms(String valStr) throws Exception {
		RightList rightList = new RightList();
		try {
			if ("".equals(valStr)) {
				valStr = " ";
			}
			String sql1 = "SELECT * FROM SYS_LEFT_MENU WHERE MENU_ID IN (   "
				+ " SELECT MENU_ID FROM SYS_LEFT_MENU WHERE MENU_NAME LIKE '%"
				+ valStr
				+ "%' "
				+ " UNION "
				+ " SELECT MODUAL_ID FROM (SELECT FM.MODUAL_ID ,(SELECT FUNC_NAME FROM SYS_FUNCTIONS F WHERE F.FUNC_ID = FM.FUNC_ID ) FUNC_NAME FROM SYS_PERMS_MODUAL_FUNC FM) AF WHERE AF.FUNC_NAME LIKE '%"
				+ valStr
				+ "%'"
				+ " UNION "
				+ " SELECT MODUAL_ID FROM SYS_PERMS_MODUAL_FUNC WHERE FUNC_ID IN (SELECT FUNC_ID FROM (SELECT B.*,(SELECT BUTN_NAME FROM ((SELECT A.DS_ID, A.BUTN_NAME, A.BUT_CODE FROM SYS_BUTNS A WHERE A.ISSHOW = 'T'"
				+ " UNION "
				+ " SELECT B.DS_ID, B.BUTN_NAME, B.BUT_CODE FROM SYS_BUTTONS B WHERE B.ISSHOW = 'T') BUTNS) "
				+ " WHERE BUTNS.DS_ID = B.BUTN_ID AND BUTNS.BUT_CODE = B.BUTN_CODE) BUTN_NAME "
				+ " FROM SYS_PERMS_FUNC_BUTN B ) AB WHERE AB.BUTN_NAME LIKE '%"
				+ valStr + "%'))"; // 模块

			// String sql2 =
			// "SELECT * FROM (SELECT MF.*,(SELECT FUNC_NAME FROM SYS_FUNCTIONS F WHERE F.FUNC_ID = MF.FUNC_ID ) FUNC_NAME "
			// +
			// "FROM SYS_PERMS_MODUAL_FUNC MF ) AF WHERE AF.FUNC_NAME  LIKE '%"+valStr+"%'";
			// //功能
			String sql2 = "SELECT AF.MODUAL_ID,AF.FUNC_ID,AF.FUNC_NAME FROM (SELECT MF.*,"
				+ " (SELECT FUNC_NAME FROM SYS_FUNCTIONS F WHERE F.FUNC_ID = MF.FUNC_ID ) FUNC_NAME"
				+ " FROM SYS_PERMS_MODUAL_FUNC MF ) AF WHERE AF.FUNC_NAME  LIKE '%"
				+ valStr
				+ "%'"
				+ " UNION "
				+ " SELECT MF.MODUAL_ID,MF.FUNC_ID,(SELECT FUNC_NAME FROM SYS_FUNCTIONS F "
				+ " WHERE F.FUNC_ID = MF.FUNC_ID) FUNC_NAME FROM SYS_PERMS_MODUAL_FUNC MF"
				+ " WHERE MF.FUNC_ID IN (SELECT FUNC_ID FROM (SELECT B.*,(SELECT BUTN_NAME FROM ((SELECT A.DS_ID, A.BUTN_NAME, A.BUT_CODE FROM SYS_BUTNS A "
				+ " WHERE A.ISSHOW = 'T' UNION SELECT B.DS_ID,B.BUTN_NAME, B.BUT_CODE FROM SYS_BUTTONS B WHERE B.ISSHOW = 'T') BUTNS) WHERE BUTNS.DS_ID = B.BUTN_ID"
				+ " AND BUTNS.BUT_CODE = B.BUTN_CODE) BUTN_NAME FROM SYS_PERMS_FUNC_BUTN B) AB WHERE AB.BUTN_NAME LIKE '%"
				+ valStr + "%')";

			String sql3 = "SELECT * FROM (SELECT B.*,(SELECT BUTN_NAME FROM ((SELECT A.DS_ID, A.BUTN_NAME, A.BUT_CODE FROM SYS_BUTNS A WHERE A.ISSHOW = 'T'"
				+ " UNION "
				+ " SELECT B.DS_ID, B.BUTN_NAME, B.BUT_CODE FROM SYS_BUTTONS B WHERE B.ISSHOW = 'T') BUTNS) "
				+ " WHERE BUTNS.DS_ID = B.BUTN_ID AND BUTNS.BUT_CODE = B.BUTN_CODE) BUTN_NAME "
				+ " FROM SYS_PERMS_FUNC_BUTN B ) AB WHERE AB.BUTN_NAME LIKE '%"
				+ valStr + "%'"; // 按钮
			List moList = this.jdbcTemplate.queryForList(sql1);
			rightList.setModelList(moList);
			List gnList = this.jdbcTemplate.queryForList(sql2);
			rightList.setDataStroeList(gnList);
			List butnList = this.jdbcTemplate.queryForList(sql3);
			rightList.setButnList(butnList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rightList;
	}

	@Override
	public boolean saveFindUserPerms(String oid, PermsGroup allList, String uid)
	throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		CallableStatement cs = null;
		List<List<String>> mList = new ArrayList<List<String>>();
		List<List<String>> gList = new ArrayList<List<String>>();
		List<List<String>> bList = new ArrayList<List<String>>();
		String flagStr = "";
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			String everyFlag = UUID32.newGuid();
			mList = allList.getoList(); // 模块List
			gList = allList.getgList(); // 功能List
			bList = allList.getbList(); // 按钮List
			flagStr = allList.getFlagStr();
			// 往缓存表中插数据，为用户和模块
			String sql = " INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,O_ID,MODUAL_ID,FLAG,EVERY_FLAG,HAVEFLAG,NONEFLAG) VALUES(SYS_GUID(),'"
				+ oid + "',?,'0','" + everyFlag + "',?,?)";
			// 往缓存表中插数据，为用户，模块，功能
			String sql2 = " INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,O_ID,MODUAL_ID,FUNC_ID,FLAG,EVERY_FLAG,HAVEFLAG,NONEFLAG) VALUES(SYS_GUID(),'"
				+ oid + "',?,?,'1','" + everyFlag + "',?,?)";
			// 往缓存表中插数据，为用户，模块，功能，按钮Id，按钮Code
			String sql3 = "INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,O_ID,MODUAL_ID,FUNC_ID,BUTN_ID,BUTN_CODE,FLAG,EVERY_FLAG,HAVEFLAG,NONEFLAG) VALUES(SYS_GUID(),'"
				+ oid + "',?,?,?,?,'2','" + everyFlag + "',?,?)";
			for (int i = 0; i < mList.size(); i++) { // 循环模块List
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, mList.get(i).get(0).toString());
				pstmt.setString(2, mList.get(i).get(1).toString());
				pstmt.setString(3, mList.get(i).get(2).toString());
				pstmt.execute();
				pstmt.close();
			}
			for (int i = 0; i < gList.size(); i++) { // 循环功能List
				pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1, gList.get(i).get(0).toString());
				pstmt.setString(2, gList.get(i).get(1).toString());
				pstmt.setString(3, gList.get(i).get(2).toString());
				pstmt.setString(4, gList.get(i).get(3).toString());
				pstmt.execute();
				pstmt.close();
			}
			for (int i = 0; i < bList.size(); i++) {
				pstmt = conn.prepareStatement(sql3);
				pstmt.setString(1, bList.get(i).get(0).toString());
				pstmt.setString(2, bList.get(i).get(1).toString());
				pstmt.setString(3, bList.get(i).get(2).toString());
				pstmt.setString(4, bList.get(i).get(3).toString());
				pstmt.setString(5, bList.get(i).get(4).toString());
				pstmt.setString(6, bList.get(i).get(5).toString());
				pstmt.execute();
				pstmt.close();
			}
			String sql4 = "{Call PK_SYS_PERMS.up_sysRight(?,?,?,?,?,?)}";
			cs = conn.prepareCall(sql4);
			cs.setString(1, "");
			cs.setString(2, oid);
			cs.setString(3, flagStr);
			cs.setString(4, uid);
			cs.setString(5, everyFlag);
			cs.registerOutParameter(6, Types.VARCHAR);
			cs.execute();
			String delSql = "DELETE FROM SYS_PERMS_MEMORY WHERE EVERY_FLAG='"
				+ everyFlag + "'";
			boolean result = this.executeSQL(delSql);
			if (result) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			if (cs != null) {
				cs.close();
			}
			if (conn != null) {
				conn.close();
			}
		}

	}

	@Override
	public boolean saveFindGroupPerms(String gid, PermsGroup allList, String uid)
	throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		CallableStatement cs = null;
		List<List<String>> mList = new ArrayList<List<String>>();
		List<List<String>> gList = new ArrayList<List<String>>();
		List<List<String>> bList = new ArrayList<List<String>>();
		String flagStr = "";
		try {
			conn = this.getJdbcTemplate().getDataSource().getConnection();
			String everyFlag = UUID32.newGuid();
			mList = allList.getoList(); // 模块List
			gList = allList.getgList(); // 功能List
			bList = allList.getbList(); // 按钮List
			flagStr = allList.getFlagStr();
			// 往缓存表中插数据，为用户和模块
			String sql = " INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,GROUP_ID,MODUAL_ID,FLAG,EVERY_FLAG,HAVEFLAG,NONEFLAG) VALUES(SYS_GUID(),'"
				+ gid + "',?,'0','" + everyFlag + "',?,?)";
			// 往缓存表中插数据，为用户，模块，功能
			String sql2 = " INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,GROUP_ID,MODUAL_ID,FUNC_ID,FLAG,EVERY_FLAG,HAVEFLAG,NONEFLAG) VALUES(SYS_GUID(),'"
				+ gid + "',?,?,'1','" + everyFlag + "',?,?)";
			// 往缓存表中插数据，为用户，模块，功能，按钮Id，按钮Code
			String sql3 = "INSERT INTO SYS_PERMS_MEMORY(GMFB_ID,GROUP_ID,MODUAL_ID,FUNC_ID,BUTN_ID,BUTN_CODE,FLAG,EVERY_FLAG,HAVEFLAG,NONEFLAG) VALUES(SYS_GUID(),'"
				+ gid + "',?,?,?,?,'2','" + everyFlag + "',?,?)";
			for (int i = 0; i < mList.size(); i++) { // 循环模块List
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, mList.get(i).get(0).toString());
				pstmt.setString(2, mList.get(i).get(1).toString());
				pstmt.setString(3, mList.get(i).get(2).toString());
				pstmt.execute();
				pstmt.close();
			}
			for (int i = 0; i < gList.size(); i++) { // 循环功能List
				pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1, gList.get(i).get(0).toString());
				pstmt.setString(2, gList.get(i).get(1).toString());
				pstmt.setString(3, gList.get(i).get(2).toString());
				pstmt.setString(4, gList.get(i).get(3).toString());
				pstmt.execute();
				pstmt.close();
			}
			for (int i = 0; i < bList.size(); i++) {
				pstmt = conn.prepareStatement(sql3);
				pstmt.setString(1, bList.get(i).get(0).toString());
				pstmt.setString(2, bList.get(i).get(1).toString());
				pstmt.setString(3, bList.get(i).get(2).toString());
				pstmt.setString(4, bList.get(i).get(3).toString());
				pstmt.setString(5, bList.get(i).get(4).toString());
				pstmt.setString(6, bList.get(i).get(5).toString());
				pstmt.execute();
				pstmt.close();
			}
			String sql4 = "{Call PK_SYS_PERMS.up_sysRight(?,?,?,?,?,?)}";
			cs = conn.prepareCall(sql4);
			cs.setString(1, gid);
			cs.setString(2, "");
			cs.setString(3, flagStr);
			cs.setString(4, uid);
			cs.setString(5, everyFlag);
			cs.registerOutParameter(6, Types.VARCHAR);
			cs.execute();
			String delSql = "DELETE FROM SYS_PERMS_MEMORY WHERE EVERY_FLAG='"
				+ everyFlag + "'";
			boolean result = this.executeSQL(delSql);
			if (result) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			if (cs != null) {
				cs.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
	}

	@Override
	public List<Map<String, Object>> querySessionInfoBySql(String sql) {
		Query query = sessionFactory.getCurrentSession().createSQLQuery(sql)
		.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	@Override
	public boolean saveOrUpdate(SysSessionContorl sessionContorl) {
		List<Map<String, Object>> list = this.getJdbcTemplate().queryForList("select * from SYS_SESSION_CONTORL where USERID = '"+sessionContorl.getUserid()+"'" );
		if(list.size()==0){
			SimpleDateFormat simpleDateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String date = simpleDateTimeFormat.format(sessionContorl.getLoginTime().getTime());
			String sql = "insert into SYS_SESSION_CONTORL values('"+sessionContorl.getSessionId()+"','"+sessionContorl.getLoginIp()+"','"+sessionContorl.getDepartId()+"','"+sessionContorl.getUserName()+"',to_date('"+date+"','yyyy-mm-dd hh24:mi:ss'),'"+sessionContorl.getIsOnline()+"','"+sessionContorl.getId()+"','"+sessionContorl.getUserid()+"')";
			this.getJdbcTemplate().execute(sql);
		}
		return true;
	}

	@Override
	public boolean delBySql(String sql) {
		Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
		if (query.executeUpdate() > 0) {
			return true;
		}
		return false;
		/*
		 * @Title: delBySql
		 * 
		 * @author : FuXiangYi
		 * 
		 * @date 2014-10-27 下午04:46:35
		 * 
		 * @version
		 */
	}

	// /**
	// * 根据DSID加载对照信息
	// */
	// @Override
	// public List<SysDzSource> loadDzByDsId(String dsId) throws Exception {
	// List<SysDzSource> dzSources = new ArrayList<SysDzSource>();
	// String sql =
	// " select DZ_IDS from SYS_DZ_DSID_MAP WHERE upper(DS_ID) = upper('"+dsId+"') ";
	// List<Map<String, Object>> dzsMap =
	// this.getJdbcTemplate().queryForList(sql);
	// String dzIds = "";
	// if(dzsMap.size() > 0){
	// dzIds = (String) ((dzsMap.get(0).get("DZ_IDS") ==
	// null)?"":dzsMap.get(0).get("DZ_IDS"));
	// }
	// if (dzIds == null || "".equals(dzIds) ) { //没有配置必须加载的数据
	// //加载关联表的dsid
	// sql = " select UPDATE_TABLE from SYS_DATASTORE WHERE DS_ID= '"+dsId+"'";
	// List<Map<String, Object>> refTable =
	// this.getJdbcTemplate().queryForList(sql);
	// String upTable = "";
	// if (refTable != null && !refTable.isEmpty()) {
	// upTable = (String) ((refTable.get(0).get("UPDATE_TABLE") ==
	// null)?"":refTable.get(0).get("UPDATE_TABLE"));
	// }
	//
	// sql =
	// " SELECT DZ_ID FROM SYS_DZ_SOURCE S WHERE upper(S.SQL_TEXT) LIKE upper('%"+upTable+"%') ";
	// refTable = this.getJdbcTemplate().queryForList(sql);
	//
	// StringBuffer ids = new StringBuffer("");
	// for(int i = 0; i < refTable.size(); i ++){
	// ids.append("'"+refTable.get(i).get("DZ_ID")+"'");
	// if (i < refTable.size() - 1) {
	// ids.append(",");
	// }
	// }
	//
	// String hql = " from SysDzSource s where s.dzId in ("+ids+")";
	// Query query =
	// this.getSessionFactory().getCurrentSession().createQuery(hql);
	// dzSources = query.list();
	// }else {
	// //dzIds = a;b;c
	// String[] dzIdArr = dzIds.split(";");
	// StringBuffer ids = new StringBuffer();
	// for (int i = 0; i < dzIdArr.length; i++) {
	// ids.append("'"+dzIdArr[i]+"'");
	// if (i < dzIdArr.length - 1) {
	// ids.append(",");
	// }
	// }
	// String hql = " from SysDzSource s where s.dzId in ("+ids+")";
	// Query query =
	// this.getSessionFactory().getCurrentSession().createQuery(hql);
	// dzSources = query.list();
	// }
	// return dzSources;
	// }

	/**
	 * 根据DSID加载对照信息
	 */
	@Override
	public List<SysDzSource> loadDzDataByDsId(String dsId) throws Exception {
		List<SysDzSource> dzSources = new ArrayList<SysDzSource>();
		if (dsId.indexOf(";") > 0) {
			String[] dsIds = dsId.split(";");
			for (int i = 0; i < dsIds.length; i++) {
				if (dsIds[i] != null && !"".equals(dsIds[i])) {
					List<SysDzSource> dzDatas = returnDzList(dsIds[i]);
					for (int j = 0; j < dzDatas.size(); j++) {
						dzSources.add(dzDatas.get(j));
					}
				}
			}
		} else {
			dzSources = returnDzList(dsId);
		}

		return dzSources;
	}

	public List<SysDzSource> returnDzList(String dsId) {
		List<SysDzSource> dzSources = new ArrayList<SysDzSource>();
		String hql = "from SysDzSource where upper(dsId) like upper('%" + dsId
		+ "%') ";
		Query query = this.getSessionFactory().getCurrentSession().createQuery(
				hql);
		List<SysDzSource> dzOldData = query.list();
		if (dzOldData.size() > 0) { // 如果模糊查询能查询到数据，那么在判断是否
			for (int i = 0; i < dzOldData.size(); i++) {
				String dsIds = dzOldData.get(i).getDsId();
				if (dsIds == null || dsIds == "") {
					continue;
				}
				if (dsIds.indexOf(";") == -1) { // 说明是真实的dsId
					dzSources.add(dzOldData.get(i));
				} else {
					String[] dzDaIds = dsIds.split(";");
					for (int j = 0; j < dzDaIds.length; j++) {
						if (dzDaIds[j] != null && !"".equals(dzDaIds[j])
								&& dzDaIds[j].trim().equals(dsId.trim())) {
							dzSources.add(dzOldData.get(i));
							break;
						}
					}

				}
			}
		}
		return dzOldData;
	}

	@Override
	public List<Map<String, Object>> getListMap(String sql) {
		return getJdbcTemplate().queryForList(sql);
	}
	@Override
	public List getUser(String sql) {
		List list = new ArrayList();
		try {
			list = this.jdbcTemplate.queryForList(sql);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
